Setup informix database on Redhat Linux 5.8 with VMWARE
Add disk space
Type the following to send a rescan request:
# echo "- - -" > /sys/class/scsi_host/host0/scan
# fdisk -l
You will find the new added disk.
Partition this new disk
# fdisk /dev/sdb
Format new created partition
# mkfs.ext3 /dev/sdb1
on Redhat Linux, Edit /etc/mtab and /etc/fstab to add this new formatted partition to filesystems
Share directory on Windows
Install Samba Server on Linux system is a best practice to share a directory between Windows & Linux
systems, so you can copy the download software packages from Windows to Linux very conveniently.
To set up a shared folder on Windows for Linux to access, start by making sure your network settings are
configured to allow the connection from the other computer by opening the Network and Sharing
Center.
In the Network and Sharing Center window, click on “Change advanced sharing settings.”
For your current profile, adjust the following two settings:
- Turn on network discovery
- Turn on file and printer sharing
Click on “Save Changes” after those settings are configured. Now we can create a place on the Windows computer for the
Linux machine to see files and copy contents to. There are no limitations to what you can share out (you could
theoretically share your entire hard drive), but we will just be sharing out a folder called “Share” located on our Desktop.
Right click on the folder you’d like to share out over the network, and click Properties. Go to the Sharing tab and click
Advanced Sharing.
Check the “Share this folder” box and click on “Permissions” toward the bottom.
In the Permissions window, you can restrict access to the folder for certain accounts. To let any user have access to your
folder, just give Full Control to the Everyone user. This will allow anyone to read and write changes to the shared folder. If
you would rather restrict access to certain accounts, just remove the Everyone user and add the users you’d like to grant
access to. Note: These user accounts are on the Windows computer, not Linux.
Click OK on the Permissions and Advanced Sharing windows once you’ve made your changes. While still in the Properties
menu, click on the Security tab.
For the Linux user to have access to the shared folder, the same permissions need to be configured in this tab as what we
configured in the sharing settings. If the two settings don’t match, the most restrictive settings are the ones that will take
effect. If your desired user already has their security permissions set up (such as the geek user in our example) then you’re
good to go and can click Close.
If you need to add a user, such as Everyone, click on Edit.
Click on Add in the next menu, enter the username, and click OK.
Click OK on all the open windows, and your folder should now be shared out and accessible on your Linux computer.
Accessing the Windows Share from Linux
You should be able to mount the shared folder by using the GUI in Linux, but it’s also very easy to do with the command
line, and it’s easier to show a terminal example because it will work across many different distributions.
You’ll need the cifs-utils package in order to mount SMB shares:
# sudo apt-get install cifs-utils
After that, just make a directory and mount the share to it. In this example, we will mount the folder to our Desktop for
easy access.
mkdir ~/Desktop/Windows-Share
# sudo mount.cifs //WindowsPC/Share /home/geek/Desktop/Windows-Share -o user=geek
As you can see in the screenshot, we were prompted for the root password of the Linux machine, and then the password
for the ‘geek’ account on Windows. After running that command, we are now able to see the contents of the Windows
share and add data to it.
In case you need help understanding the mount command, here’s a breakdown:
sudo mount.cifs – This is just the mount command, set to mount a CIFS (SMB) share.
WindowsPC – This is the name of the Windows computer. Type “This PC” into the Start menu on Windows, right click it,
and go to Properties to see your computer name.
//Windows-PC/Share – This is the full path to the shared folder.
/home/geek/Desktop/Windows-Share – This is where we’d like the share to be mounted.
-o user=geek – This is the Windows username that we are using to access the shared folder.
Creating the Share on Linux
To set up a shared folder on Linux for Windows to access, start with installing Samba.
# sudo apt-get install samba
After Samba installs, configure a username and password that will be used to access the share.
# smbpasswd -a geek
Note: In this example, we are using ‘geek’ since we already have a Linux user with that name – but you can choose any
name you’d like.
Create the directory that you’d like to share out to your Windows computer. We’re just going to put a folder on our
Desktop.
mkdir ~/Desktop/Share
Now, use your favorite editor to configure the smb.conf file.
# sudo vi /etc/samba/smb.conf
Scroll down to the end of the file and add these lines:
[<folder_name>]
path = /home/<user_name>/<folder_name>
available = yes
valid users = <user_name>
read only = no
browsable = yes
public = yes
writable = yes
Obviously, you’ll need to replace some of the values with your personal settings. It should look something like this:
Save the file and close your editor. Now, restart the SMB service for the changes to take effect.
sudo service smbd restart
Your shared folder should now be accessible from a Windows PC.
Accessing the Linux Share from Windows
Now, let’s add the Linux share to our Windows Desktop. Right-click somewhere on your Desktop and go to New > Shortcut.
Type in the network location of the shared folder, with this syntax:
\\IP-ADDRESS\SHARE-NAME
If you need the IP of your Linux computer, just issue the following command:
# ifconfig
Click Next, choose a name for the Shortcut, and click Finish. You should end up with a Shortcut on your Desktop that goes
right to the Linux share.
# chmod –R lchen:root /Server
Another way to share Windows Drive (directory) with VMWare Linux Server:
Install informix 11.7 on RH Linux5.8 64-bit
Create user/group: informix/informix
[root@db2cm64 home]# mkdir informix
[root@db2cm64 home]# chmod -R 755 informix
[root@db2cm64 home]# chown -R informix:informix informix
[root@db2cm64 informix]# cd /Server/informix; ./ids_install
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
Launching installer...
Preparing CONSOLE Mode Installation...
===============================================================================
IBM Informix Software Bundle (created with InstallAnywhere)
-------------------------------------------------------------------------------
===============================================================================
Getting started with IBM Informix Software Bundle
-------------------------------------------------
InstallAnywhere will guide you through the installation of IBM Informix
Software Bundle.
Copyright IBM Corporation 1996, 2012. All rights reserved.
1. Release Notes
The Release Notes can be found in
/Server/informix/SERVER/doc/ids_unix_relnotes_11.70.html
2. Installation Guide
Please view the Installation / Quick Beginnings Guide at
/Server/informix/SERVER/doc/ids_unix_installg_11.70.pdf
3. Launch Information Center
Access the IDS 11.70 Information Center at
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp
To Begin Installation,
Respond to each prompt to proceed to the next step in the installation.
If you want to change something on a previous step, type 'back'.
You may cancel this installation at any time by typing 'quit'.
PRESS <ENTER> TO CONTINUE:
===============================================================================
International License Agreement for Non-Warranted Programs
Part 1 - General Terms
BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
"ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
THESE TERMS,
* DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
OR USE THE PROGRAM; AND
* PROMPTLY RETURN THE UNUSED MEDIA AND DOCUMENTATION TO THE PARTY FROM
WHOM IT WAS OBTAINED FOR A REFUND OF THE AMOUNT PAID. IF THE PROGRAM
WAS DOWNLOADED, DESTROY ALL COPIES OF THE PROGRAM.
1. Definitions
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.:
"Authorized Use" - the specified level at which Licensee is authorized
to execute or run the Program. That level may be measured by number of
users, millions of service units ("MSUs"), Processor Value Units
("PVUs"), or other level of use specified by IBM.
"IBM" - International Business Machines Corporation or one of its
subsidiaries.
"License Information" ("LI") - a document that provides information
and any additional terms specific to a Program. The Program's LI is
available at www.ibm.com/software/sla. The LI can also be found in the
Program's directory, by the use of a system command, or as a booklet
included with the Program.
"Program" - the following, including the original and all whole or
partial copies: 1) machine-readable instructions and data, 2)
components, files, and modules, 3) audio-visual content (such as
images, text, recordings, or pictures), and 4) related licensed
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.: 1
===============================================================================
Installation Goals
------------------
What do you want to accomplish?
->1- Install products and features
2- Extract the product files (-DLEGACY option)
3- Create an RPM image for redistribution
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT::
===============================================================================
Installation Location
---------------------
Choose location for software installation
Default Install Folder: /opt/IBM/informix
ENTER AN ABSOLUTE PATH, OR PRESS <ENTER> TO ACCEPT THE DEFAULT
:
===============================================================================
Installation Type
-----------------
Select the installation type.
Typical: Install the database server with all features and a database server that
is configured with default values. Includes:
** Client Software Development Kit (CSDK)
** Java Database Connectivity (JDBC)
Minimum disk space required: 700-800MB
Custom: Install the database server with specific features and software that you need.
Optionally install a configured database server instance.
Minimum disk space required: 75 MB (without a server instance)
->1- Typical
2- Custom
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT::
===============================================================================
Server Instance Creation
------------------------
Create a server instance?
->1- Yes - create an instance
2- No - do not create an instance
ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS <ENTER> TO ACCEPT THE DEFAULT::
===============================================================================
International License Agreement for Non-Warranted Programs
Part 1 - General Terms
BY DOWNLOADING, INSTALLING, COPYING, ACCESSING, CLICKING ON AN
"ACCEPT" BUTTON, OR OTHERWISE USING THE PROGRAM, LICENSEE AGREES TO
THE TERMS OF THIS AGREEMENT. IF YOU ARE ACCEPTING THESE TERMS ON
BEHALF OF LICENSEE, YOU REPRESENT AND WARRANT THAT YOU HAVE FULL
AUTHORITY TO BIND LICENSEE TO THESE TERMS. IF YOU DO NOT AGREE TO
THESE TERMS,
* DO NOT DOWNLOAD, INSTALL, COPY, ACCESS, CLICK ON AN "ACCEPT" BUTTON,
OR USE THE PROGRAM; AND
* PROMPTLY RETURN THE UNUSED MEDIA AND DOCUMENTATION TO THE PARTY FROM
WHOM IT WAS OBTAINED FOR A REFUND OF THE AMOUNT PAID. IF THE PROGRAM
WAS DOWNLOADED, DESTROY ALL COPIES OF THE PROGRAM.
1. Definitions
Press Enter to continue viewing the license agreement, or enter "1" to
accept the agreement, "2" to decline it, "3" to print it, or "99" to go back
to the previous screen.: 1
===============================================================================
Installation Summary
--------------------
Please review the following before continuing:
Product Name:
IBM Informix Software Bundle
Install Folder:
/opt/IBM/informix
Product Features:
IBM Informix database server,
Base Server,
Extensions and tools,
J/Foundation,
Database extensions,
Conversion and reversion support,
XML publishing,
Demonstration database scripts,
Enterprise Replication,
Data loading utilities,
onunload and onload utilities,
dbload utility,
High-Performance Loader,
Backup and Restore,
archecker utility,
ON-Bar utility,
Informix Storage Manager,
Informix interface to Tivoli Storage Manager,
Administrative utilities,
Performance monitoring utilities,
Miscellaneous monitoring utilities,
Auditing utilities,
Database import and export utilities,
IBM Informix Client SDK,
IBM Informix Object Interface for C++,
IBM Informix Object Interface for C++ demos,
IBM Informix ESQL/C,
7.2 application compatibility module,
IBM Informix ESQL/C demos,
IBM Informix LIBDMI for client applications,
IBM Informix ODBC Driver,
IBM Informix ODBC Driver demos,
Global Language Support (GLS),
West European and Americas,
East European and Slavic,
Japanese,
Korean,
Chinese,
Thai,
IBM Informix JDBC
Server name:
ol_informix1170
Server DRDA alias:
Server number:
0
TCP/IP port number:
16697
Total instance size:
437 MB
Total memory (bufferpool + user):
129 MB
Bufferpool allocation:
97 MB
Number of processors:
1
Data storage location:
/opt/IBM/informix/ol_informix1170/dbspaces
Disk Space Information (for Installation Target):
Required: 1,048,477,120 bytes
Available: 30,246,674,432 bytes
PRESS <ENTER> TO CONTINUE:
===============================================================================
Ready To Install
----------------
InstallAnywhere is now ready to install IBM Informix Software Bundle onto your
system at the following location:
/opt/IBM/informix
PRESS <ENTER> TO INSTALL:
===============================================================================
Installing...
-------------
[==================|==================|==================|==================]
[------------------|------------------|------------------|------------------]
===============================================================================
Server Initialization
---------------------
The server will now be initialized. Initialization might take quite a while,
depending on the performance of your computer.
PRESS <ENTER> TO CONTINUE:
===============================================================================
Using the new instance
----------------------
The IBM Informix Software Bundle created a database server instance. If you
selected to initialize the instance and to display a command prompt, the
instance is ready to use.
If you selected to initialize the instance and chose not to display a command
prompt, you can go to /opt/IBM/informix on a command line and run one of the
following commands, where ol_informix1170 is the name of the path or file where
the instance is installed:
Windows:
ol_informix1170.cmd
UNIX csh:
source ol_informix1170.csh
UNIX ksh or bourne:
./ol_informix1170.ksh
If you selected to initialize the instance and it fails to run, check the
online.log file to verify that initialization was successful.
In addition, if you used an existing configuration file during the
installation, ensure that the root chunk exists, is owned by user and group
informix, and has readable and writable (rw) permission bits set for owner and
group only.
PRESS <ENTER> TO CONTINUE:
===============================================================================
Installation Complete
---------------------
Congratulations! IBM Informix Software Bundle installation is complete.
Product install status:
IBM Informix 11.70: Successful
IBM Informix Client-SDK: Successful
IBM Informix JDBC Driver: Successful
IBM OpenAdmin Tool for Informix: Successful
For more information about using Informix products, see the IBM Informix 11.70
Information Center at
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp.
PRESS <ENTER> TO EXIT THE INSTALLER:
Configure Linux System for informix
1. [informix@ibmserver ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
# export PATH
. ~/ol_informix1170.ksh
2. [informix@ibmserver ~]$ cat .netrc
machine ipdev login lchen password admin12
machine ifx01 login lchen password admin12
3. [informix@ibmserver ~]$ tail /etc/services
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp
db2c_db2inst1 50000/tcp
CMIC 8084/tcp
ol_informix1170 25337/tcp
dr_informix1170 32300/tcp
ipdbsvc 6800/tcp #New ipdb database instance
ardbsvc 6900/tcp #New ardb database instance
systestdbsvc 6600/tcp # system test database
Load Tables between two instance/database using unload/load utility
4. Create chunck file for informix dbspace
# mount /ix_dat
# touch /ix_dat/ix_dat.1
# touch /ix_dat/ix_dat.2
# touch /ix_dat/ix_dat.3
# touch /ix_dat/ix_dat.4
# chown –R informix:informix /ix_dat
5. Create a 1G dbspace <datadbs1>
# su – informix
$ onspaces -c -d datadbs1 -p /ix_dat/ix_dat.1 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Space successfully added.
** WARNING ** A level 0 archive of Root DBSpace will need to be
done.
6. Add other three 1G chuck file to this datadbs1 dbspace
$ onspaces -a datadbs1 -p /ix_dat/ix_dat.2 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
$ onspaces -a datadbs1 -p /ix_dat/ix_dat.3 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
$ onspaces -a datadbs1 -p /ix_dat/ix_dat.4 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
7. Drop database <sysclrdb> using dbaccess
DROP DATABASE >>
Enter the name of the database you wish to drop.
----------------------- @ol_informix1170 ------- Press CTRL-W for Help
--------
sysadmin@ol_informix1170
sysclrdb@ol_informix1170
sysmaster@ol_informix1170
sysuser@ol_informix1170
sysutils@ol_informix1170
Tips: Delete this database, because I will setup a new test database exactly same with
production system.
8. Create a new database using dbaccess:
database name: ip_0p
Log: No
CREATE DATABASE >> ip_0p
Enter the name you want to assign to the new database, then press
Return.
------------------------------------------------ Press CTRL-W for Help
--------
select the dbspace <datadbs1> as this database <ip_0p>’s db2space
Tips: chucks(files) make up dbspace, database resident on dbspaces, and tables, as well as
index,routine, etc, resident on database.
9. Create schema on the source production informix server ifx01:
$ dbschema –d ip_0p ip_0p.sql
10. Ftp ip_0p.sql to Linux server db2cm64, and run this sql to setup the database ip_0p for testing
$ dbaccess –d ip_0p.sql
11. On production server ifx01, use dbaccess, unload Table: lii_client, lii_account and client_invoice
to files and ftp these files to Linux server db2cm64
ALTER TABLE ip_0p:informix.hs_duty_rate DROP CONSTRAINT u208_791;
UNLOAD TO "/home/lchen/ifx01.lii_client" SELECT * FROM lii_client;
UNLOAD TO "/home/lchen/ifx01.lii_account" SELECT * FROM lii_account;
UNLOAD TO "/home/lchen/ifx01.client_invoice" SELECT * FROM
client_invoice;
ALTER TABLE ip_0p:informix.hs_duty_rate ADD CONSTRAINT hs_duty_rate_PK
PRIMARY KEY (hsno,hstarifftrtmnt,effdate);
12. On Linux server db2cm64, use dbaccess. load these files to tables:
LOAD FROM "/home/lchen/ifx01.client" INSERT INTO lii_client;
LOAD FROM "/home/lchen/ifx01.account" INSERT INTO lii_account;
LOAD FROM "/home/lchen/ifx01.client_invoice" INSERT INTO
client_invoice;
13. Change the database to U log mode(un-buffer), which is the normal database log setting
$ ontape -s -U ip_0p
Tips: Load large file (Table), It is a good idea to change the database log mode to No Log mode
$ ontape -s -N ip_0p
14. Alter a table to turn off/on logging mode
$ dbaccess
ALTER TABLE client_invoice TYPE (RAW)
ALTER TABLE client_invoice TYPE (STANDARD)
Adjust the size of log files to prevent long transactions
Use larger log files when many users are writing to the logs at the same time. If you use small logs and
long transactions are likely to occur, reduce the high-watermark. Set the LTXHWM value to 50 and the
LTXEHWM value to 60.
If the log files are too small, the database server might run out of log space while rolling back a long
transaction. In this case, the database server cannot block fast enough to add a new log file before the
last one fills. If the last log file fills, the system hangs and displays an error message. To fix the problem,
shut down and restart the database server.
Add more tempdbs space to build (set) contrains, indexs for a large table.
$ onspaces -a tempdbs -p /ix_dat/ix_temp.1 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
< Run SQL in $ dbaccess >
SET CONSTRAINTS,INDEXES,TRIGGERS FOR client_invoice ENABLED;
Load Table between two instance/database using SQL
1. setup the informix environment on Linux Server.
There 3 files you should modify, so you can connect to and run sql on another instance on different
servers without prompting for username and password.
# su – informix
$ ls -la
total 28
drwxr-xr-x 2 informix informix 4096 Sep 11 17:13 .
drwxr-xr-x 5 root root 4096 Sep 8 22:42 ..
-rw------- 1 informix informix 326 Sep 11 14:24 .bash_history
-rwxr-xr-x 1 informix informix 259 Sep 8 23:02 .bash_profile
-rw------- 1 informix informix 137 Sep 11 17:13 .netrc
-rw------- 1 informix informix 975 Sep 11 17:13 .viminfo
$ chmod 600 .netrc
$ more .netrc
machine ifx01 login lchen password admini@12
machine ipdev login lchen password admini@12
machine db2cm64 login lchen password admini@12
$ more /opt/IBM/informix/etc/sqlhost.ol_informix1170
ol_informix1170 onsoctcp db2cm64 ol_informix1170
dr_informix1170 drsoctcp db2cm64 dr_informix1170
ipdb onsoctcp ifx01 ipdbsvc
systestdb onsoctcp ipdev systestdbsvc
-bash-3.2$ tail -10 /etc/services
ol_informix1170 8166/tcp
dr_informix1170 15103/tcp
systestdbsvc 6600/tcp
ipdbsvc 6800/tcp
2. Run SQL in $dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop
Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help ---
-----
INSERT INTO b3
SELECT * FROM ip_systest@systestdb:informix.b3;
$ onstat –
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line (CKPT REQ) (LONGTX) -- Up 00:45:36 -- 173796 Kbytes
Blocked:CKPT LONGTX
$ onstat –m
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line (CKPT REQ) (LONGTX) -- Up 00:46:23 -- 173796 Kbytes
Blocked:CKPT LONGTX
Message Log File: /opt/IBM/informix/ol_informix1170.log
09:00:33 Performance Advisory: Based on the current workload, the physical log might be too small to
accommodate the time it takes to flush the buffer pool.
09:00:33 Results: The server might block transactions during checkpoints.
09:00:33 Action: If transactions are blocked during the checkpoint, increase the size of the
physical log to at least 103436 KB.
09:00:33 Performance Advisory: The physical log is too small for automatic checkpoints.
09:00:33 Results: Automatic checkpoints are disabled.
09:00:33 Action: To enable automatic checkpoints, increase the physical log to at least 103436 KB.
09:00:34 Performance Advisory: The physical log is running out of room during checkpoint processing.
09:00:34 Results: Transactions are being blocked until the checkpoint is complete.
09:00:34 Action: Increase the physical log size.
09:00:35 Checkpoint Completed: duration was 1 seconds.
09:00:35 Tue Aug 21 - loguniq 140, logpos 0xa85174, timestamp: 0xc4f7861 Interval: 1313
09:00:35 Maximum server connections 3
09:00:35 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 1, Plog used 11316, Llog used 8661
09:00:36 Logical Log 140 Complete, timestamp: 0xc5227b0.
09:00:37 Logical Log Files are Full -- Backup is Needed
You need to Backup Log Files, change Log Tape device to /dev/null using onmonitor before you do the
log backup.
$ export TERM vt200
$ onmonitor
INITIALIZATION: Make desired changes and press ESC to record changes.
Press Interrupt to abort changes. Press F2 or CTRL-F for field-level help.
DISK PARAMETERS
Page Size [ 2] Kbytes Mirror [N]
Tape Dev. [/ix_tmp/tapedev ] Block Size [ 32] Kbytes Total Tape Size [ 0] Kbytes
Log Tape Dev. [/x_tmp/ltapedev ] Block Size [ 32] Kbytes Total Tape Size [ 0] Kbytes Stage Blob [ ]
Root Name [rootdbs ] Root Size [ 200000] Kbytes
Primary Path [/opt/IBM/informix/ol_informix1170/dbspaces/rootdbs ] Root Offset [ 0] Kbytes
Mirror Path [ ] Mirror Offset [ 0] Kbytes
Phy. Log Size [ 30176] Kbytes Log. Log Size [ 10000] Kbytes Number of Logical Logs [ 14]
Enter the log tape device pathname
Tips: You can define Tape Device as above, and then use symbolic link to any device you want to use:
ln –s /dev/null /ix_tmp/tapedev
ln –s /dev/null /ix_tmp/ltapedev
$ ontape -a
Performing automatic backup of logical logs.
Please mount tape 1 on /opt/IBM/informix/ltapedev and press Return to continue ...
Do you want to back up the current logical log? (y/n) y
Read/Write End Of Medium enabled: blocks = 4337
Please label this tape as number 1 in the log tape sequence.
This tape contains the following logical logs:
128 - 142
Program over.
-bash-3.2$ onstat -l
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line -- Up 01:07:05 -- 181988
Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 48 64 30627 565 54.21
phybegin physize phypos phyused %used
2:6325 15088 8266 2376 15.75
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-3 10 32 1701136 67273 3868 25.3 17.4
Subsystem numrecs Log Space used
OLDRSAM 1701128 132793268
HA 8 352
address number flags uniqid begin size used %used
4b840c50 7 U-B---- 134 3:53 4608 4608 100.00
4b840cb8 8 U-B---- 135 3:4661 4608 4608 100.00
4b840d20 3 U-B---- 136 2:53 4608 4608 100.00
4b840d88 4 U-B---- 137 1:2953 4608 4608 100.00
4b840df0 6 U-B---- 138 1:12169 4608 4608 100.00
4b840e58 13 U-B---- 139 1:36043 4608 4608 100.00
4b840ec0 14 U-B---- 140 1:40651 4608 4608 100.00
4b840f28 5 U-B---- 141 1:7561 4608 4608 100.00
4b840f90 9 U-B---- 142 3:9269 4608 4608 100.00
4dddde98 15 U---C-L 143 1:45259 4608 1262 27.39
4dd1ab48 16 A------ 0 1:49867 4608 0 0.00
4b6f9ea8 10 U-B---- 129 3:13877 4608 4608 100.00
4b6f9f10 11 U-B---- 130 3:18485 4608 4608 100.00
4b6f9f78 1 U-B---- 131 1:24475 4608 4608 100.00
4b6fa438 2 U-B---- 132 1:29083 4608 4608 100.00
4b826450 12 U-B---- 133 3:23093 4608 4608 100.00
16 active, 16 total
-bash-3.2$ onstat -m
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line -- Up 01:09:43 -- 181988
Kbytes
Message Log File: /opt/IBM/informix/ol_informix1170.log
09:46:41 Logical Log 138 - Backup Started
09:46:41 Logical Log 138 - Backup Completed
09:46:41 Logical Log 139 - Backup Started
09:46:41 Logical Log 139 - Backup Completed
09:46:41 Logical Log 140 - Backup Started
09:46:41 Logical Log 140 - Backup Completed
09:46:41 Logical Log 141 - Backup Started
09:46:41 Logical Log 141 - Backup Completed
09:46:49 Logical Log 142 - Backup Started
09:46:49 Dynamically added log file 16 to DBspace 1
09:46:51 Checkpoint Completed: duration was 0 seconds.
09:46:51 Tue Aug 21 - loguniq 143, logpos 0x2a4, timestamp: 0xc56eecd Interval: 1316
09:46:51 Maximum server connections 3
09:46:51 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog
used 7252, Llog used 4606
09:46:51 Logical Log 142 - Backup Completed
09:46:53 Long Transaction 0x4b829930 Aborted. Rollback Duration: 2784 Seconds
09:46:54 Logical Log 141 Complete, timestamp: 0xc57db60.
09:46:54 Logical Log 142 Complete, timestamp: 0xc57db60.
How many locks one user thread may hold, and how many write calls the user thread have executed , if
more LOCKS are needed:
$onstat –u
$ onstat -c | grep LOCKS
# LOCKS - The initial number of locks when Informix starts.
LOCKS 3000000
Tips: Load large file (Table), It is a good idea to change the database log mode to No Log mode
$ ontape -s -N ip_0p
So I have to do this again, clean the backup device and release disk space first.
$ cat /dev/null > /opt/IBM/informix/tapedev
Disconnect all session from database ip_0p and close this database.
$ ontape –s N ip_0p
Please enter the level of archive to be performed (0, 1, or 2) 0
Archive failed - Error changing logging status - 'ip_0p'. iserrno 107.
Program over.
-bash-3.2$ ontape -s -N ip_0p
Please enter the level of archive to be performed (0, 1, or 2) 0
Please mount tape 1 on /opt/IBM/informix/tapedev and press Return to continue ...
10 percent done.
20 percent done.
30 percent done.
40 percent done.
50 percent done.
60 percent done.
70 percent done.
80 percent done.
100 percent done.
Read/Write End Of Medium enabled: blocks = 36226
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
143
Program over.
Add other three 1G chuck file to logdbs
$ onspaces -a logdbs -p /ix_dat/ix_llog.1 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
$ onparams -a -d logdbs -s 900000 -i
Log operation started. To monitor progress, use the onstat -l command.
Logical log successfully added.
$ onstat -l
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line -- Up 01:39:19 -- 181988
Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 0 64 96094 1687 56.96
phybegin physize phypos phyused %used
2:6325 15088 14363 7 0.05
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-3 0 32 3608414 140850 6284 25.6 22.4
Subsystem numrecs Log Space used
OLDRSAM 3608393 280934956
HA 21 924
address number flags uniqid begin size
used %used
4b840c50 7 U------ 150 3:53 4608 4608
100.00
4b840cb8 8 U------ 151 3:4661 4608 4608
100.00
4b840d20 3 U------ 152 2:53 4608 4608
100.00
4b840d88 4 U------ 153 1:2953 4608 4608
100.00
4b840df0 6 U------ 154 1:12169 4608 4608
100.00
4b840e58 13 U------ 155 1:36043 4608 4608
100.00
4b840ec0 14 U------ 156 1:40651 4608 4608
100.00
4b840f28 5 U------ 157 1:7561 4608 4608
100.00
4b840f90 9 U------ 158 3:9269 4608 4608
100.00
4e59c330 17 U---C-L 159 1:63518 4608 887
19.25
4e59c228 19 A------ 0 12:3 450000 0
0.00
4e59c100 18 A------ 0 1:68126 4608 0
0.00
4dddde98 15 U------ 143 1:45259 4608 4608
100.00
4dd1ab48 16 U------ 144 1:49867 4608 4608
100.00
4b6f9ea8 10 U------ 145 3:13877 4608 4608
100.00
4b6f9f10 11 U------ 146 3:18485 4608 4608
100.00
4b6f9f78 1 U------ 147 1:24475 4608 4608
100.00
4b6fa438 2 U------ 148 1:29083 4608 4608
100.00
4b826450 12 U------ 149 3:23093 4608 4608
100.00
19 active, 19 total
$ onstat -d
IBM Informix Dynamic Server Version 11.70.FC5DE -- On-Line -- Up 01:40:13 -- 181988
Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
4b6fa028 1 0x60001 1 1 2048 N BA informix
rootdbs
4b826558 2 0x40001 2 1 2048 N BA informix
physdbs
4b826700 3 0x60001 3 2 2048 N BA informix
logdbs
4b8268a8 4 0x40001 4 1 2048 N BA informix
datadbs
4b826a50 5 0x48001 5 1 2048 N SBA informix
sbspace
4b826bf8 6 0x42001 6 2 2048 N TBA informix
tempdbs
4b826da0 7 0x40001 7 4 2048 N BA informix
datadbs1
7 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
4b6fa1d0 1 1 0 100000 39805 PO-B--
/opt/IBM/informix/ol_informix1170/dbspaces/rootdbs
4b6fa4a0 2 2 0 25088 5339 PO-B--
/opt/IBM/informix/ol_informix1170/dbspaces/plogdbs
4b6fa6a0 3 3 0 30720 3019 PO-B--
/opt/IBM/informix/ol_informix1170/dbspaces/llogdbs
4b6fa8a0 4 4 0 25600 25547 PO-B--
/opt/IBM/informix/ol_informix1170/dbspaces/datadbs
4b6faaa0 5 5 0 16384 15205 15205 POSB--
/opt/IBM/informix/ol_informix1170/dbspaces/sbspace
Metadata 1126 837 1126
4b6faca0 6 6 0 25600 25547 PO-B--
/opt/IBM/informix/ol_informix1170/dbspaces/tempdbs
4d709028 7 7 0 500000 0 PO-B--
/ix_dat/ix_dat.1
4d709228 8 7 0 500000 405789 PO-B--
/ix_dat/ix_dat.2
4d709428 9 7 0 500000 499997 PO-B--
/ix_dat/ix_dat.3
4d709628 10 7 0 500000 499997 PO-B--
/ix_dat/ix_dat.4
4d709828 11 6 0 500000 499997 PO-B--
/ix_dat/ix_temp.1
4dd1abb0 12 3 0 500000 49997 PO-B--
/ix_dat/ix_llog.1
12 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
Again!!!
-bash-3.2$ touch ix_llog.2
-bash-3.2$ ls -l
total 6005920
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:42 ix_dat.1
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:42 ix_dat.2
-rw-rw---- 1 informix informix 1024000000 Aug 17 10:36 ix_dat.3
-rw-rw---- 1 informix informix 1024000000 Aug 17 10:36 ix_dat.4
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:44 ix_llog.1
-rw-rw-r-- 1 informix informix 0 Aug 21 10:44 ix_llog.2
-rw-rw---- 1 informix informix 1024000000 Aug 21 08:40 ix_temp.1
drw-rw---- 2 informix informix 16384 Aug 17 09:38 lost+found
-bash-3.2$ chmod 660 ix_llog.2
-bash-3.2$ ls -l
total 6005920
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:44 ix_dat.1
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:44 ix_dat.2
-rw-rw---- 1 informix informix 1024000000 Aug 17 10:36 ix_dat.3
-rw-rw---- 1 informix informix 1024000000 Aug 17 10:36 ix_dat.4
-rw-rw---- 1 informix informix 1024000000 Aug 21 10:44 ix_llog.1
-rw-rw---- 1 informix informix 0 Aug 21 10:44 ix_llog.2
-rw-rw---- 1 informix informix 1024000000 Aug 21 08:40 ix_temp.1
drw-rw---- 2 informix informix 16384 Aug 17 09:38 lost+found
-bash-3.2$ onspaces -a logdbs -p /ix_dat/ix_llog.2 -o 0 -s 1000000
Verifying physical disk space, please wait ...
Chunk successfully added.
-bash-3.2$ onparams -a -d logdbs -s 999900 -i
Log operation started. To monitor progress, use the onstat -l command.
Logical log successfully added.
$ dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
INSERT INTO b3
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE EXTEND(TO_DATE(approveddate,"%Y/%m/%d %H:%M:%S"),YEAR TO SECOND) <
(EXTEND(current, YEAR TO SECOND) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7)
MONTH TO MONTH);
Using TEMP table to guarantee the Data insert into archive DB is exactly the same with the data deleted
from the original production resource table
To disable logging on temporary tables, set the TEMPTAB_NOLOG configuration parameter to 1.
# TEMPTAB_NOLOG - Controls the default logging mode for temporary
TEMPTAB_NOLOG 0
$ onmode -wf TEMPTAB_NOLOG=1
17:01:52 Value of TEMPTAB_NOLOG has been changed to 1.
$ onmode -wm TEMPTAB_NOLOG=1
17:02:00 Value of TEMPTAB_NOLOG has been changed to 1.
$ dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE EXTEND(TO_DATE(approveddate,"%Y/%m/%d %H:%M:%S"),YEAR TO SECOND) >
(EXTEND(current, YEAR TO SECOND) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7) MONTH
TO MONTH)
INTO TEMP tmp_b3;
INSERT INTO b3 SELECT * FROM tmp_b3 t_b3
WHERE t_b3.b3iid NOT EXSITS (SELECT b3iid FROM b3);
DELETE FROM ip_systest@systestdb:informix.b3 o_b3
WHERE o_b3.b3iid IN (SELECT b3iid FROM tmp_b3);
b3: 3,021,376,578 byte
b3b: 1,070,955 byte
containers: 2,682,988 byte
status_history: 698,664,792 byte
b3_subheader: 715,614,824 byte
b3_line: 14,957,060,547 byte
b3_line_comment: 471,820 byte
b3_recap_details: 6,377,817,173 byte
TIPS: Add more io vp to tuning the IO performance
$ onmode –p +10 io
$ onmode –p +10 cpu
When Using TEMP table, Add more tempdbs space
Firstly, Delete formal chunck, only because demo license version cannot support so many chuncks.
-bash-3.2$ onspaces -d tempdbs -p /ix_tmp/ix_temp.1 -o 0
WARNING: Dropping a chunk.
Do you really want to continue? (y/n)y
Chunk successfully dropped.
** WARNING ** A level 0 archive for DBspace tempdbs will need to be done
before '/ix_dat/ix_temp.1' can be reused (see Dynamic Server Administrator's manual).
$ cat /dev/null > /ix_tmp/ix_temp.1
$ onspaces -a tempdbs -p /ix_tmp/ix_temp.1 -o 0 -s 4000000
Verifying physical disk space, please wait ...
Chunk successfully added.
$dbaccess
SET CONSTRAINTS,INDEXES,TRIGGERS FOR b3b DISABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR containers DISABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR status_history DISABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR b3 DISABLED;
Then, we drop the primary key definition from b3, and we turn off the table log of b3:
$ dbaccess
DROP INDEX <>;
ALTER TABLE b3 DROP CONSTRAINT <>
ALTER TABLE b3 TYPE (RAW)
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE EXTEND(TO_DATE(approveddate,"%Y/%m/%d %H:%M:%S"),YEAR TO SECOND) >
(EXTEND(current, YEAR TO SECOND) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7) MONTH TO MONTH)
INTO TEMP tmp_b3;
INSERT INTO b3 SELECT * FROM tmp_b3
CREATE INDEX <> ON b3 (b3iid);
ALTER TABLE b3 ADD CONSTRAINT primary key (b3iid);
Table altered.
SET CONSTRAINTS,INDEXES,TRIGGERS FOR b3 ENABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR status_history ENABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR containers ENABLED;
SET CONSTRAINTS,INDEXES,TRIGGERS FOR b3b ENABLED;
ALTER TABLE b3 TYPE (standard)
alter table "informix".containers add constraint (foreign key
(b3iid) references "informix".b3 );
alter table "informix".containers add b3b (foreign key
(b3iid) references "informix".b3 );
alter table "informix".containers add status_history (foreign key
(b3iid) references "informix".b3 );
After I load data from Add Primary key CONSTRAINT to TABLE b3 column (b3iid);
$ dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE EXTEND(TO_DATE(approveddate,"%Y/%m/%d %H:%M:%S"),YEAR TO SECOND) <
(EXTEND(current, YEAR TO SECOND) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7) MONTH TO MONTH)
INTO TEMP tmp_b3;
option
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE approveddate >= '2011/03/01' and approveddate < '2011/04/01'
INTO TEMP tmp_b3;
option
DELETE FROM b3 WHERE b3iid IN (SELECT b3iid FROM tmp_b3)
180162 row(s) deleted.
To solve log files space issue:
$ onstat -c | grep LTX
# LTXHWM - The percentage of the logical logs that can be
# LTXEHWM - The percentage of the logical logs that have been
# LTXHWM and LTXEHWM because the server can add new logical logs
# If dynamic logging is off, set LTXHWM and LTXEHWM to
# When using Enterprise Replication, set LTXEHWM to at least 30%
# higher than LTXHWM to minimize log overruns.
LTXHWM 70
LTXEHWM 80
$ onmode -wm LTXEHWM=100
09:58:27 Value of LTXEHWM has been changed to 100.
$ onmode -wf LTXEHWM=100
09:58:37 Value of LTXEHWM has been changed to 100.
$ onmode -wm LTXHWM=100
09:58:52 Value of LTXHWM has been changed to 100.
$ onmode -wf LTXHWM=100
09:58:58 Value of LTXHWM has been changed to 100.
Turn on database ip_0p log mode
$ ontape -s -U ip_0p
Please enter the level of archive to be performed (0, 1, or 2) 0
Please mount tape 1 on /ix_tmp/tapedev and press Return to continue ...
10 percent done.
20 percent done.
30 percent done.
40 percent done.
50 percent done.
60 percent done.
70 percent done.
80 percent done.
90 percent done.
100 percent done.
Read/Write End Of Medium enabled: blocks = 134992
Please label this tape as number 1 in the arc tape sequence.
This tape contains the following logical logs:
17
Program over.
$dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
SELECT * FROM ip_systest@systestdb:informix.b3
WHERE EXTEND(TO_DATE(approveddate,"%Y/%m/%d %H:%M:%S"),YEAR TO SECOND) <
(EXTEND(current, YEAR TO SECOND) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7) MONTH TO MONTH)
INTO TEMP tmp_b3;
INSERT INTO b3 SELECT * FROM tmp_b3 WHERE b3iid NOT IN (select b3iid from b3);
180162 row(s) inserted.
INSERT INTO b3b SELECT * FROM ip_systest@systestdb:informix.b3b
INSERT INTO containers SELECT * FROM ip_systest@systestdb:informix.containers
INSERT INTO status_history SELECT * FROM ip_systest@systestdb:informix.status_history
INSERT INTO containers SELECT * FROM ip_systest@systestdb:informix.containers
WHERE b3iid NOT IN (SELECT b3iid from containers)
Insert large table piece by piece using rowid
$dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
insert into b3 select * from ip_systest@systestdb:informix.b3
where rowid >5000000 and rowid < 15000000
create trigger "informix".td_b3 delete on "informix".b3 referencing old as old_del for each row
(
execute procedure "informix".pd_b3(old_del.b3iid ));
create procedure "informix".pd_b3(old_b3iid integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Delete all children in "b3_subheader"
delete from b3_subheader
where b3iid = old_b3iid;
-- Delete all children in "b3b"
delete from b3b
where b3iid = old_b3iid;
-- Delete all children in "status_history"
delete from status_history
where b3iid = old_b3iid;
-- Delete all children in "containers"
delete from containers
where b3iid = old_b3iid;
end procedure;
create procedure "informix".pd_b3_subheader(old_b3subiid integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Delete all children in "b3_line"
delete from b3_line
where b3subiid = old_b3subiid;
end procedure;
create procedure "informix".pd_b3_line(old_b3lineiid integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Delete all children in "b3_recap_details"
delete from b3_recap_details
where b3lineiid = old_b3lineiid;
-- Delete all children in "b3_line_comment"
delete from b3_line_comment
where b3lineiid = old_b3lineiid;
end procedure;
create procedure "informix".pd_rpt_b3(old_b3iid integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Delete all children in "rpt_b3_subheader"
delete from rpt_b3_subheader
where b3iid = old_b3iid;
end procedure;
create procedure "informix".pi_b3(new_liiclientno integer,
new_liiaccountno integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Parent "lii_account" must exist when inserting a child in "b3"
if new_liiclientno is not null and
new_liiaccountno is not null then
select count(*)
into numrows
from lii_account
where liiclientno = new_liiclientno
and liiaccountno = new_liiaccountno;
if (numrows = 0) then
let errno = -1002;
let errmsg = "Parent does not exist in ""lii_account"". Cannot create child in ""b3"".";
raise exception -746, 0, errmsg;
end if;
end if;
end procedure;
create procedure "informix".pi_b3b(new_b3iid integer)
define errno integer;
define errmsg char(255);
define numrows integer;
-- Parent "b3" must exist when inserting a child in "b3b"
if new_b3iid is not null then
select count(*)
into numrows
from b3
where b3iid = new_b3iid;
if (numrows = 0) then
let errno = -1002;
let errmsg = "Parent does not exist in ""b3"". Cannot create child in ""b3b"".";
raise exception -746, 0, errmsg;
end if;
end if;
end procedure;
Synchronize tables between production table with development table, which has a unique constraint
with two column
$dbaccess
SQL: New Run Modify Use-editor Output Choose Save Info Drop Exit
Run the current SQL statements.
----------------------- ip_systest@systestdb --- Press CTRL-W for Help --------
insert into lii_client select * from ip_0p@ipdb:informix.lii_client
where liiclientno NOT IN (select liiclient from lii_client);
insert into lii_account select * from ip_0p@ipdb:informix.lii_account r
where (select count(*) from lii_account l
where r.liiclientno=l.liiclientno and r.liiaccountno=l.liiaccountno)
= 0;
Archive and Purge B3 Table
$dbaccess
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
drop procedure archiveandpurge()
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
drop PROCEDURE insertarch
-bash-3.2$ dbaccess ip_0p@ol_informix1170 < insertarch.sql
-bash-3.2$ dbaccess ip_0p@ol_informix1170 < archiveandpurge.sql
Database selected.
Routine created.
Database closed.
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
CREATE PROCEDURE "informix".archiveandpurge() RETURNING CHAR(20), CHAR(20), INT;
--Define Working variables
DEFINE startdate CHAR(20);
DEFINE enddate CHAR(20);
DEFINE archivecount INT;
DEFINE archiveDay DATE;
LET startdate = EXTEND(current, YEAR TO MONTH) - INTERVAL(1) YEAR TO YEAR - INTERVAL(7) MONTH
TO MONTH;
LET enddate = EXTEND(current, YEAR TO MONTH) - INTERVAL(1) YEAR TO YEAR - INTERVAL(6) MONTH TO
MONTH;
LET archiveDay = TODAY;
EXECUTE PROCEDURE insertArch(startdate, enddate);
SELECT COUNT(*)
INTO archivecount
FROM reporterr
WHERE currentday = archiveDay;
IF archivecount = 0 THEN
-- EXECUTE PROCEDURE deleteB3(startdate, enddate);
END IF
RETURN startdate, enddate, archivecount;
END PROCEDURE;
CREATE PROCEDURE "informix".insertarch(startdate CHAR(20),enddate CHAR(20))
-- Declare b3 table columns
DEFINE s_b3iid INT;
DEFINE s_liiclientno INT;
DEFINE s_liiaccountno INT;
DEFINE s_liibrchno INT;
DEFINE s_liirefno INT;
DEFINE s_acctsecurno INT;
DEFINE s_b3type CHAR(2);
DEFINE s_cargcntrlno CHAR(25);
DEFINE s_carriercode CHAR(4);
DEFINE s_createdate CHAR(20);
DEFINE s_custoff CHAR(4);
DEFINE s_k84date CHAR(20);
DEFINE s_modetransp CHAR(2);
DEFINE s_portunlading CHAR(4);
DEFINE s_reldate CHAR(20);
DEFINE s_status INT;
DEFINE s_totb3duty float;
DEFINE s_totb3exctax float;
DEFINE s_totb3gst float;
DEFINE s_totb3sima float;
DEFINE s_totb3vfd float;
DEFINE s_transno INT;
DEFINE s_weight INT;
DEFINE s_purchaseorder1 CHAR(15);
DEFINE s_purchaseorder2 CHAR(15);
DEFINE s_shipvia CHAR(18);
DEFINE s_locationofgoods CHAR(17);
DEFINE s_containerno CHAR(20);
DEFINE s_vendorname CHAR(25);
DEFINE s_vendorstate CHAR(3);
DEFINE s_vendorzip CHAR(10);
DEFINE s_freight float;
DEFINE s_usportexit CHAR(5);
DEFINE s_billoflading CHAR(10);
DEFINE s_cargcntrlqty float;
DEFINE s_approveddate CHAR(20);
--Define Working variables
DEFINE tableName CHAR(25);
DEFINE currentDay DATE;
DEFINE mode CHAR(1);
DEFINE sqlErr INT;
DEFINE isamErr INT;
-- Trap Exception
ON EXCEPTION SET sqlErr, isamErr
CALL reportErr(currentDay,tableName,mode, s_b3iid, sqlErr,isamErr);
END EXCEPTION WITH RESUME;
SET LOCK MODE TO WAIT 60;
LET currentDay = today;
LET tableName = 'B3';
LET mode = 'I';
LET s_b3iid = NULL;
FOREACH WITH HOLD
SELECT b3iid, liiclientno, liiaccountno, liibrchno, liirefno, acctsecurno, b3type,
cargcntrlno, carriercode, createdate, custoff, k84date, modetransp,
portunlading, reldate, status, totb3duty, totb3exctax, totb3gst,
totb3sima, totb3vfd, transno, weight, purchaseorder1, purchaseorder2,
shipvia, locationofgoods, containerno, vendorname, vendorstate, vendorzip,
freight, usportexit, billoflading, cargcntrlqty, approveddate
INTO s_b3iid, s_liiclientno, s_liiaccountno, s_liibrchno, s_liirefno, s_acctsecurno
,
s_b3type, s_cargcntrlno, s_carriercode, s_createdate, s_custoff, s_k84date,
s_modetransp, s_portunlading, s_reldate, s_status, s_totb3duty,
s_totb3exctax, s_totb3gst, s_totb3sima, s_totb3vfd, s_transno, s_weight,
s_purchaseorder1, s_purchaseorder2, s_shipvia, s_locationofgoods, s_containerno,
s_vendorname, s_vendorstate, s_vendorzip, s_freight, s_usportexit,
s_billoflading, s_cargcntrlqty, s_approveddate
FROM ip_0p@ipdb:informix.b3
-- WHERE approveddate >= '2011/03' and approveddate < '2011/04'
WHERE approveddate >= startdate and approveddate < enddate
BEGIN
-- Trap Exception
ON EXCEPTION SET sqlErr, isamErr
CALL reportErr(currentDay,tableName,mode, s_b3iid, sqlErr,isamErr);
END EXCEPTION WITH RESUME;
insert into b3
values(s_b3iid, s_liiclientno, s_liiaccountno, s_liibrchno, s_liirefno, s_acctsecurn
o,
s_b3type, s_cargcntrlno, s_carriercode, s_createdate, s_custoff, s_k84date,
s_modetransp, s_portunlading, s_reldate, s_status, s_totb3duty,
s_totb3exctax, s_totb3gst, s_totb3sima, s_totb3vfd, s_transno, s_weight,
s_purchaseorder1, s_purchaseorder2, s_shipvia, s_locationofgoods, s_containerno,
s_vendorname, s_vendorstate, s_vendorzip, s_freight, s_usportexit,
s_billoflading, s_cargcntrlqty, s_approveddate);
END
END FOREACH;
END PROCEDURE;
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
select count(*) from ip_0p@ipdb:informix.b3
where approveddate like "2011/04/%"
(count(*))
275047
------------ ip_0p@ol_informix1170 ------------- Press CTRL-W for Help --------
execute procedure insertarch(‘2011/03’,’2011/04’)
-bash-3.2$ cd /home/informix/scripts/local/b3_arch
-bash-3.2$ . ./autoArchive.ksh
Database selected.
(expression) (expression) (expression)
2011/02/01 00:00:00 2011/03/01 00:00:00 1
1 row(s) retrieved.
Database closed.
You have mail in /var/spool/mail/root
[lchen@ifx01 /home/lchen] $ lspv
hdisk2 00ca32fde4198d51 livedbvg active
hdisk3 00ca32fde4198fc0 archdbvg active
hdisk4 00ca32fde41a128f appsvg active
hdisk0 00ca32fd35a97b39 rootvg active
hdisk1 00ca32fd35a97d46 rootvg active
[lchen@ifx01 /home/lchen] $ lsvg archdbvg
VOLUME GROUP: archdbvg VG IDENTIFIER: 00ca32fd00004c00000001101750a843
VG STATE: active PP SIZE: 256 megabyte(s)
VG PERMISSION: read/write TOTAL PPs: 399 (102144 megabytes)
MAX LVs: 256 FREE PPs: 4 (1024 megabytes)
LVs: 9 USED PPs: 395 (101120 megabytes)
OPEN LVs: 9 QUORUM: 2 (Enabled)
TOTAL PVs: 1 VG DESCRIPTORS: 2
STALE PVs: 0 STALE PPs: 0
ACTIVE PVs: 1 AUTO ON: yes
MAX PPs per VG: 32512
MAX PPs per PV: 1016 MAX PVs: 32
LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no
HOT SPARE: no BB POLICY: relocatable
PV RESTRICTION: none
[lchen@ifx01 /home/lchen] $ lsvg -l archdbvg
archdbvg:
LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT
achrootlv jfs2 1 1 1 open/syncd /ach_root
achploglv jfs2 1 1 1 open/syncd /ach_plog
achlloglv jfs2 4 4 1 open/syncd /ach_llog
achdat1lv jfs2 172 172 1 open/syncd /ach_dat1
achdat2lv jfs2 184 184 1 open/syncd /ach_dat2
achidx1lv jfs2 12 12 1 open/syncd /ach_idx1
achidx2lv jfs2 12 12 1 open/syncd /ach_idx2
achtemplv jfs2 8 8 1 open/syncd /ach_temp
loglv01 jfs2log 1 1 1 open/syncd N/A
[lchen@ifx01 /home/lchen] $ df -k
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 2883584 2641184 9% 11947 2% /
/dev/hd2 8126464 5121204 37% 71000 6% /usr
/dev/hd9var 3145728 566632 82% 10547 8% /var
/dev/hd3 5242880 4290560 19% 203 1% /tmp
/dev/hd1 2621440 2140584 19% 2275 1% /home
/proc - - - - - /proc
/dev/hd10opt 7864320 7178244 9% 11894 1% /opt
/dev/ibmlv 10485760 10187912 3% 3564 1% /ibm
/dev/achrootlv 262144 11776 96% 5 1% /ach_root
/dev/netinslv 2621440 2620700 1% 4 1% /netins
/dev/dmqjtmplv 13107200 3334044 75% 1735 1% /dmqjtmp
/dev/recyclelv 15728640 6690080 58% 6064 1% /recyclebox
/dev/achlloglv 1048576 48088 96% 5 1% /ach_llog
/dev/achdat1lv 45088768 1081536 98% 48 1% /ach_dat1
/dev/achdat2lv 48234496 226784 100% 52 1% /ach_dat2
/dev/achidx1lv 3145728 144920 96% 7 1% /ach_idx1
/dev/achidx2lv 3145728 144920 96% 7 1% /ach_idx2
/dev/achtemplv 2097152 72504 97% 6 1% /ach_temp
/dev/appslv 10485760 6351088 40% 20368 2% /usr/apps
/dev/achploglv 262144 11776 96% 5 1% /ach_plog
/dev/ixrootlv 262144 46576 83% 5 1% /ix_root
/dev/ixploglv 262144 5776 98% 5 1% /ix_plog
/dev/ixlloglv 1048576 48280 96% 5 1% /ix_llog
/dev/ixdat1lv 23068672 1064760 96% 26 1% /ix_dat1
/dev/ixdat2lv 26214400 1209968 96% 29 1% /ix_dat2
/dev/ixdat3lv 19922944 919572 96% 23 1% /ix_dat3
/dev/ixidx1lv 7340032 338556 96% 11 1% /ix_idx1
/dev/ixidx2lv 5242880 241732 96% 9 1% /ix_idx2
/dev/ixidx3lv 4194304 193336 96% 8 1% /ix_idx3
/dev/ixtemplv 4194304 193336 96% 8 1% /ix_temp
/dev/insightlv 2097152 1987312 6% 3050 1% /insight
/dev/livedump 262144 261776 1% 4 1% /var/adm/ras/livedump
/dev/hd11admin 524288 523864 1% 5 1% /admin
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
50431810 1 0x1 1 1 4096 N informix rootdbs
5051dd50 2 0x1 2 1 4096 N informix llogdbs
5051deb0 3 0x1 3 2 4096 N informix tempdbs1
5138a018 4 0x1 4 1 4096 N informix plogdbs
5138a178 5 0x1 5 44 4096 N informix datadbs1
5138a2d8 6 0x1 27 48 4096 N informix datadbs2
5138a438 7 0x1 51 3 4096 N informix indxdbs1
5138a598 8 0x1 54 3 4096 N informix indxdbs2
< 51390928 52 7 0 250000 1698 PO-- /ach_idx1/ach_idx1.2
< 51390af8 53 7 0 250000 249997 PO-- /ach_idx1/ach_idx1.3
< 51390cc8 54 8 0 250000 177497 PO-- /ach_idx2/ach_idx2.1
---
> 51390928 52 7 0 250000 162 PO-- /ach_idx1/ach_idx1.2
> 51390af8 53 7 0 250000 245901 PO-- /ach_idx1/ach_idx1.3
> 51390cc8 54 8 0 250000 176857 PO-- /ach_idx2/ach_idx2.1
117,119c117,119
< 51399928 100 6 0 250000 182409 PO-- /ach_dat2/ach_dat2.47
< 51399af8 101 6 0 250000 249997 PO-- /ach_dat2/ach_dat2.48
< 51399cc8 102 5 0 250000 211597 PO-- /ach_dat1/ach_dat1.43
---
> 51399928 100 6 0 250000 34945 PO-- /ach_dat2/ach_dat2.47
> 51399af8 101 6 0 250000 184461 PO-- /ach_dat2/ach_dat2.48
> 51399cc8 102 5 0 250000 45709 PO-- /ach_dat1/ach_dat1.43
INFO - b3: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
199_649 datadbs1 datadbs1 I
b3_rk1 indxdbs1 indxdbs1 I
b3_rk10 indxdbs2 indxdbs2 I
b3_rk2 indxdbs2 indxdbs2 I
b3_rk3 indxdbs1 indxdbs1 I
b3_rk5 indxdbs1 indxdbs1 I
b3_rk9 indxdbs1 indxdbs1 I
INFO - b3_subheader: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
200_697 datadbs1 datadbs1 I
b3_subheader_rk1 indxdbs1 indxdbs1 I
INFO - b3_line: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
201_711 datadbs2 datadbs2 I
201_841 datadbs2 datadbs2 I
INFO - b3_recap_details: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
202_753 datadbs1 datadbs1 I
202_842 datadbs1 datadbs1 I
INFO - b3_line_comment: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
153_424 datadbs2 datadbs2 I
153_837 datadbs2 datadbs2 I
INFO - b3_line_iid: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
Display fragment strategy for a table.
----------------------- ip_arch03@ardb --------- Press CTRL-W for Help --------
Idx/Tbl name Dbspace Partition Type Expression
118_113 datadbs2 datadbs2 I
$ dbschema -d ip_systest -ss ip_systest.sql
The dbschema -ss option generates server-specific information. In all Informix® database servers except SE, the -ss option
always generates the lock mode, extent sizes, and the dbspace name if the dbspace name is different from the database
dbspace. In addition, if tables are fragmented, the -ss option displays information about the fragmentation strategy.
